package com.olap.starter.common.util;

import com.baomidou.mybatisplus.annotation.DbType;
import com.olap.starter.core.exception.OnlineException;
import com.olap.starter.common.vo.ExecuteQuery;
import com.olap.starter.common.vo.Parameter;
import com.olap.starter.common.vo.QueryParameter;
import com.olap.starter.util.sql.jsqlparser.JsqlParserUtil;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author WBG
 * @date 2022/11/14 10:42
 * @describe
 */
public class QueryGeneratorUtil {


    public static ExecuteQuery initQuery(QueryParameter queryParameter) {
        try {
            PlainSelect plainSelect = JsqlParserUtil.getPlainSelect(queryParameter.getSql());
            String plSql = plainSelect.toString();
            if (plainSelect.getGroupBy() != null || plainSelect.getGroupBy() != null) {
                plSql = plSql.substring(0, plSql.indexOf(plainSelect.getGroupBy().toString()));
                List<Expression> expressions = plainSelect.getGroupBy().getGroupByExpressionList().getExpressions();
                if (expressions != null) {
                    List<String> groupBy = queryParameter.getGroupBy();
                    if (groupBy == null) {
                        groupBy = new ArrayList<>();
                    }
                    for (Expression expression : expressions) {
                        groupBy.add(expression.toString());
                    }
                    queryParameter.setGroupBy(groupBy);
                }

                List<OrderByElement> orderByElements = plainSelect.getOrderByElements();
                if (orderByElements != null) {
                    List<String> orderBy = queryParameter.getOrderBy();
                    if (orderBy == null) {
                        orderBy = new ArrayList<>();
                    }
                    for (OrderByElement orderByElement : orderByElements) {
                        orderBy.add(orderByElement.toString());
                    }
                    queryParameter.setOrderBy(orderBy);
                }
            }
            queryParameter.setSql(plSql.replaceAll("`", ""));
        } catch (Exception e) {

        }
        ExecuteQuery executeQuery = ExecuteQueryUtil.init(queryParameter);
        StringBuilder ws = executeQuery.getWs();
        String exSql;
        if (StringUtils.isNotBlank(ws)) {
            if (ws.length() > 1) {
                ws.insert(0, " WHERE ");
            }
            exSql = queryParameter.getSql() + ws;
        } else {
            exSql = queryParameter.getSql();
        }
        exSql = group(queryParameter, exSql);

        String exCountSql = "SELECT count(1) as total FROM(" + exSql + ")a";
        exSql = order(queryParameter, exSql);
        executeQuery.setExSql(page(queryParameter, exSql));
        executeQuery.setExCountSql(exCountSql);
        setExecuteSql(executeQuery);
        queryParameter.setExecuteSql(executeQuery.getExSqlStr());
        return executeQuery;
    }

    private static void setExecuteSql(ExecuteQuery executeQuery) {
        String originalString = replacePlaceholdersWithQuestionMarks(executeQuery.getExSql());
        Object[] replacements = getSqlAndParams(executeQuery);
        String exSqlStr = getExecuteSql(originalString, replacements);
        executeQuery.setExSqlStr(exSqlStr);
    }

    public static String getExecuteSql(String originalString, Object[] replacements) {
        String[] parts = originalString.split("\\?");
        if (replacements != null && replacements.length != 0) {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < parts.length; i++) {
                sb.append(parts[i]);
                if (i < replacements.length) {
                    sb.append("'").append(replacements[i]).append("'");
                }
            }
            return sb.toString();
        }
        return originalString;
    }

    public static String replacePlaceholdersWithQuestionMarks(String input) {
        Pattern pattern = Pattern.compile("#\\{[^}]+\\}");
        Matcher matcher = pattern.matcher(input);
        StringBuffer sb = new StringBuffer();

        while (matcher.find()) {
            matcher.appendReplacement(sb, "?");
        }

        matcher.appendTail(sb);
        return sb.toString();
    }

    public static List<String> extractPlaceholders(String input, String type) {
        List<String> placeholders = new ArrayList<>();
        Pattern pattern = Pattern.compile(type + "\\{([^}]+)\\}");
        Matcher matcher = pattern.matcher(input);

        while (matcher.find()) {
            placeholders.add(matcher.group(1).trim()); // group(1) 是捕获组中的内容，即 #{} 内的部分
        }

        return placeholders;
    }

    public static Object[] getSqlAndParams(ExecuteQuery executeQuery) {
        String exSql = executeQuery.getExSql();
        int size = executeQuery.getAm() != null ? executeQuery.getAm().size() : 0;
        size += executeQuery.getOm() != null ? executeQuery.getOm().size() : 0;
        if (size == 0) {
            return null;
        }
        List<String> keys = extractPlaceholders(exSql, "#");
        Object[] obj = new Object[keys.size()];
        for (int i = 0; i < keys.size(); i++) {
            String[] key = keys.get(i).split("\\.");
            if (key.length == 1) {
                obj[i] = executeQuery.getAm().get(key[0]);
            } else {
                obj[i] = "om".equals(key[0]) ? executeQuery.getOm().get(key[1]) : executeQuery.getAm().get(key[1]);
            }
        }
        return obj;
    }

    public static String order(QueryParameter queryParameter, String sql) {
        List<String> list = queryParameter.getOrderBy();
        StringBuilder sb = null;

        if (StringUtils.isNotBlank(queryParameter.getOrderByStr())) {
            if (queryParameter.getOrderByStr().toUpperCase().indexOf(" ORDER ") == -1) {
                sql += " ORDER BY ";
            }
            sql += queryParameter.getOrderByStr();
            sb = new StringBuilder(" , ");
        }else{
            sb = new StringBuilder(" ORDER BY ");
        }

        if (list != null && list.size() > 0) {
            boolean first = true;
            for (String s : list) {
                if (first) {
                    first = false;
                } else {
                    sb.append(",");
                }
                sb.append(s);
            }
            sql = sql + sb + " ";
        }
        return sql;
    }

    public static String group(QueryParameter queryParameter, String sql) {
        List<String> listGroup = queryParameter.getGroupBy();
        if (listGroup != null && listGroup.size() > 0) {
            StringBuilder sb = new StringBuilder();
            boolean first = true;
            sb.append(" GROUP BY ");
            for (String s : listGroup) {
                if (first) {
                    first = false;
                } else {
                    sb.append(",");
                }
                sb.append(s);
            }
            sql = sql + sb;
        }
        return sql;
    }

    public static String page(QueryParameter queryParameter, String sql) {
        if (queryParameter.isEnablePage()) {
            if (DbType.ORACLE.getDb().equals(queryParameter.getDbType()) || DbType.SQL_SERVER.getDb().equals(queryParameter.getDbType())) {
                if (DbType.SQL_SERVER.getDb().equals(queryParameter.getDbType()) && sql.trim().toLowerCase().indexOf("order by") == -1) {
                    sql += " ORDER BY (SELECT NULL) ";
                }
                sql = OracleQueryUtil.page(sql, getStartNumber(queryParameter.getPageNumber(), queryParameter.getPageSize()), queryParameter.getPageSize());
            } else {
                sql = limit(sql, getStartNumber(queryParameter.getPageNumber(), queryParameter.getPageSize()), queryParameter.getPageSize());
            }
            //sql = "SELECT " + sql.trim().substring(6, sql.trim().length());
        }
        return sql;
    }

    public static String limit(String querySelect, int offset, int limit) {
        return compress(querySelect) + " limit " + offset + " ," + limit;
    }

    public static String count(String querySelect) {
        querySelect = compress(querySelect);
        int orderIndex = getLastOrderInsertPoint(querySelect);
        int formIndex = getAfterFormInsertPoint(querySelect);
        String select = querySelect.substring(0, formIndex);
        if (select.toLowerCase().indexOf("select distinct") != -1 || querySelect.toLowerCase().indexOf("group by") != -1) {
            return new StringBuffer(querySelect.length()).append(
                    "select count(1) as total from (").append(
                    querySelect, 0, orderIndex).append(" ) t").toString();
        }
        return new StringBuffer(querySelect.length()).append(
                "select count(1) as total ").append(
                querySelect, formIndex, orderIndex).toString();
    }

    private static int getLastOrderInsertPoint(String querySelect) {
        int orderIndex = querySelect.toUpperCase().lastIndexOf("ORDER BY");
        if (orderIndex == -1) {
            orderIndex = querySelect.length();
        } else {
            if (!isBracketCanPartnership(querySelect.substring(orderIndex))) {
                throw new OnlineException("排序必须要有ORDER BY语句!");
            }
        }
        return orderIndex;
    }

    public static int getStartNumber(int pageNumber, int pageSize) {
        if (pageNumber == 0) {
            pageNumber = 1;
        }
        return (pageNumber - 1) * pageSize;
    }

    private static String compress(String sql) {
        return sql.replaceAll("[\r\n]", " ").replaceAll("\\s{2,}", " ");
    }

    private static boolean isBracketCanPartnership(String text) {
        if (text == null
                || (getIndexOfCount(text, '(') != getIndexOfCount(text, ')'))) {
            return false;
        }
        return true;
    }

    private static int getIndexOfCount(String text, char ch) {
        int count = 0;
        for (int i = 0; i < text.length(); i++) {
            count = (text.charAt(i) == ch) ? count + 1 : count;
        }
        return count;
    }

    private static int getAfterFormInsertPoint(String querySelect) {
        String regex = "\\s+FROM\\s+";
        Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(querySelect);
        while (matcher.find()) {
            int fromStartIndex = matcher.start(0);
            String text = querySelect.substring(0, fromStartIndex);
            if (isBracketCanPartnership(text)) {
                return fromStartIndex;
            }
        }
        return 0;
    }/*

    public static void main(String[] args) {
        QueryParameter queryParameter = new QueryParameter();
        queryParameter.setEnablePage(false);
        List<Parameter> list = new ArrayList<>();
        Parameter parameter = new Parameter();
        parameter.setKey("mainTable");
        parameter.setValue("t");
        list.add(parameter);
        Parameter parameter1 = new Parameter();
        parameter1.setKey("mainTable");
        parameter1.setValue("t2");
        list.add(parameter1);
        queryParameter.setQueryParameterListAnd(list);
        queryParameter.setSql("select * from table_config ");
        ExecuteQuery executeQuery = initQuery(queryParameter);
        System.out.println(executeQuery);
    }*/
}
